package com.example.springboot.utils.excel;

import com.example.springboot.utils.DateUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;


/**
 * 数据导出成excel,response相应输出
 *
 * @author xieyang
 */
@SuppressWarnings({"deprecation", "unchecked", "resource"})
public class ExcelUtil {

    public static Logger logger = Logger.getLogger(ExcelUtil.class);
    private static Workbook wb;

    private static CellStyle titleStyle; // 标题行样式
    private static Font titleFont; // 标题行字体
    private static CellStyle dateStyle; // 日期行样式
    private static Font dateFont; // 日期行字体
    private static CellStyle headStyle; // 表头行样式
    private static Font headFont; // 表头行字体
    private static CellStyle contentStyle; // 内容行样式
    private static Font contentFont; // 内容行字体
    private static final String EXTENSION_XLS = "xls";
    private static final String EXTENSION_XLSX = "xlsx";

    /**
     * 解析excel并返回内容信息，以sheet作为key
     *
     * @param excelResourceFile
     * @return
     * @throws Exception
     */
    public static List<SheetContent> getExcelInfo(File excelResourceFile) throws Exception {

        if (excelResourceFile == null || (!excelResourceFile.exists())) {
            // 判断文件是否存在
            throw new Exception("file is not exist!");
        } else {
            // 文件路径
            String path = excelResourceFile.getPath();
            Workbook workbook = null;
            if (path.endsWith(EXTENSION_XLS)) {
                // excel2003版本
                workbook = new HSSFWorkbook(new FileInputStream(excelResourceFile));
            } else if (path.endsWith(EXTENSION_XLSX)) {
                // excel2007版本以上
                workbook = new XSSFWorkbook(new FileInputStream(excelResourceFile));
            }

            if (workbook != null) {
                return parseExcel(workbook);
            }

        }
        return null;
    }

    /**
     * 解析Excel
     *
     * @param workbook
     * @return
     * @throws Exception
     */
    private static List<SheetContent> parseExcel(Workbook workbook) {
        // excel解析结果存储引用
        List<SheetContent> sheetContents = new ArrayList<SheetContent>();
        try {
            // 获取excel中包含的sheet总数
            int sheetNumber = workbook.getNumberOfSheets();
            // 遍历sheet
            for (int i = 0; i < sheetNumber; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) {
                    // sheet为空时，继续循环
                    continue;
                }
                // 单个sheet中行开始编号
                int firstRowNumIndex = sheet.getFirstRowNum();
                // 单个sheet中行结束编号
                int lastRowNumIndex = sheet.getLastRowNum();
                // sheet内容存储对象
                SheetContent sheetContent = new SheetContent();
                sheetContent.setSheetName(sheet.getSheetName());
                // 除表头行以外的存储对象
                Map<Object, List<Object>> sheetContentMap = new HashMap<Object, List<Object>>();

                // 遍历sheet
                for (int j = firstRowNumIndex; j <= lastRowNumIndex; j++) {
                    // 获取行对象
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        break;
                    }
                    List<Object> contentList = new ArrayList<Object>();
                    // 遍历单个行的列
                    for (int m = row.getFirstCellNum(); m <= row.getLastCellNum(); m++) {
                        contentList.add(getCellValue(row.getCell(m), true));
                    }
                    sheetContentMap.put(row.getRowNum(), contentList);
                }
                // 将单个sheet的内容保存到此map中
                sheetContent.setSheetContentMap(sheetContentMap);
                sheetContents.add(sheetContent);
            }

        } catch (Exception e) {
            logger.error("Parse Excel fail , error: " + e);
        }

        return sheetContents;
    }

    /**
     * 获取cell中的值
     *
     * @param cell
     * @param treatAsStr
     * @return
     */
    private static String getCellValue(Cell cell, boolean treatAsStr) {
        if (cell == null) {
            return "";
        }

        if (treatAsStr) {
            // 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
            // 加上下面这句，临时把它当做文本来读取
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }

        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }

    /**
     * excel文件写出
     *
     * @param request
     * @param response
     * @param fileName
     */
    public static void writeExcelToResponse(HttpServletRequest request, HttpServletResponse response, String fileName, Workbook workbook) {
        try {
            response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xlsx");
            response.setContentType("Application/msexcel;charset=utf-8");
            OutputStream fOut = response.getOutputStream();
            workbook.write(fOut);
        } catch (Exception e) {
            logger.error("Write excel to response error, " + e);
        }
    }

    /**
     * excel文件打包成zip文件
     *
     * @param request
     * @param response
     * @param fileName zip文件夹名称
     */
    public static void writeExcelToResponseUseZip(HttpServletRequest request, HttpServletResponse response, String fileName, Workbook[] workbooks) {
        try {
            int flag = 1;
            // fileName = URLEncoder.encode(fileName, "UTF-8");
            String zipFileName = new String(fileName.getBytes(), "ISO8859-1") + ".zip";
            response.setContentType("application/zip");
            response.setHeader("content-disposition", "attachment;filename=" + zipFileName);
            ZipOutputStream out = new ZipOutputStream(response.getOutputStream());
            for (Workbook workbook : workbooks) {
                // PS:excel写入到zip时，不能直接从workbook中写入到zip
                String excelFileName = fileName + "_" + flag + ".xlsx";
                File excelFile = new File(excelFileName);
                FileOutputStream fileOutputStream = new FileOutputStream(excelFileName);
                // 先将excel写入到文件中，后续从文件中读一次
                workbook.write(fileOutputStream);
                fileOutputStream.flush();
                fileOutputStream.close();
                ZipEntry entry = new ZipEntry(excelFileName);
                out.putNextEntry(entry);
                // 以下是读文件 操作
                FileInputStream fileInputStream = new FileInputStream(excelFileName);
                byte[] buf = new byte[2048];
                BufferedInputStream origin = new BufferedInputStream(fileInputStream, 2048);
                int len;
                while ((len = origin.read(buf, 0, 2048)) != -1) {
                    out.write(buf, 0, len);
                }
                excelFile.deleteOnExit();
                flag++;
            }
            // 写入完成后刷新
            out.finish();
        } catch (Exception e) {
            logger.error("Write excel to response error, " + e);
        }
    }

    /**
     * @throws IOException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     * @Description: 将Map里的集合对象数据输出Excel数据流
     */
    public static Workbook export2Excel(ExportExcelDataInfo dataInfo) throws IOException, IllegalArgumentException, IllegalAccessException {
        LinkedHashMap<SheetDesc, List<?>> map = dataInfo.getObjsMap();
        // 初始化excel
        init();
        Set<SheetDesc> sheetDescSet = map.keySet();
        Integer i = 0;
        for (SheetDesc sheetDesc : sheetDescSet) {
            i++;
            // 获取显示值与过滤值之间的关系
            List<FiledDescription> filedDescriptions = sheetDesc.getExcelFiledDescriptions();
            // sheet Name
            String sheetName = sheetDesc.getSheetName();
            Sheet sheet = getSheets(sheetName);

            // 创建sheet中的表头
            createTableTitleRow(sheetDesc, sheet);
            // 日期行
            createTableDateRow(sheetDesc, sheet);
            // 表头
            creatTableHeadRow(sheetDesc, sheet);
            // 设置宽度自适应
            antoChangeWith(sheet, sheetDesc.getExcelFiledDescriptions().size(), filedDescriptions);
            // 待写入数据
            List<?> objects = map.get(sheetDesc);
            int rowNum = 3;
            if (objects != null && objects.size() > 0) {
                for (Object object : objects) {
                    if (rowNum > 1000003) {
                        break;
                    }
                    Row hssfRow = sheet.createRow(rowNum);
                    int cellNum = 0;
                    for (FiledDescription filedDescription : filedDescriptions) {
                        Cell hssfCell = hssfRow.createCell(cellNum);
                        String fieldName = filedDescription.getFieldName();

                        String value = "";
                        if (object instanceof Map) {
                            Map<String, Object> mapTmp = (Map<String, Object>) object;
                            // 如果为null,用空字符串代替,如果为Date类型,格式化为标准格式yyyy-MM-dd
                            // HH:mm:ss
                            Object obj = mapTmp.get(fieldName);
                            value = obj == null ? "" : obj instanceof Date ? DateUtils.formatDatetime((Date) obj) : obj.toString();
                        } else {
                            // 如果为null,用空字符串代替,如果为Date类型,格式化为标准格式yyyy-MM-dd
                            // HH:mm:ss
                            Object obj = ReflectionUtils.invokeGetterMethod(object, fieldName);
                            value = obj == null ? "" : obj instanceof Date ? DateUtils.formatDatetime((Date) obj) : obj.toString();
                        }
                        hssfCell.setCellValue(value);
                        cellNum++;
                    }
                    rowNum++;
                }
            }
            if (dataInfo.getB() != null && i == dataInfo.getSheetNum()) {
                // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                Drawing patriarch = sheet.createDrawingPatriarch();
                // anchor主要用于设置图片的属性
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255, Short.parseShort(dataInfo.getCol1().toString()), dataInfo.getRow1(),
                        Short.parseShort(dataInfo.getCol2().toString()), dataInfo.getRow2());
                anchor.setAnchorType(3);
                // 插入图片
                patriarch.createPicture(anchor, wb.addPicture(dataInfo.getB(), XSSFWorkbook.PICTURE_TYPE_JPEG));
            }
        }
        return wb;
    }

    private static void antoChangeWith(Sheet sheet, int columnNum, List<FiledDescription> descriptions) {
        for (int i = 0; i < columnNum; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    /**
     * 初始化workBook Excel文件，设置字体及格式
     */
    private static void init() {
        wb = new SXSSFWorkbook(500);

        titleFont = wb.createFont();
        titleStyle = wb.createCellStyle();
        dateStyle = wb.createCellStyle();
        dateFont = wb.createFont();
        headStyle = wb.createCellStyle();
        headFont = wb.createFont();
        contentStyle = wb.createCellStyle();
        contentFont = wb.createFont();
        initTitleCellStyle();
        initTitleFont();
        initDateCellStyle();
        initDateFont();
        initHeadCellStyle();
        initHeadFont();
        initContentCellStyle();
        initContentFont();
    }

    /**
     * @Description: 自动调整列宽
     */
    @SuppressWarnings("unused")
    private static void adjustColumnSize(Sheet[] sheets, int sheetNum, String[] fieldNames) {
        for (int i = 0; i < fieldNames.length + 1; i++) {
            sheets[sheetNum].autoSizeColumn(i, true);
        }
    }

    /**
     * @Description: 创建标题行(需合并单元格)
     */
    private static void createTableTitleRow(SheetDesc sheetDesc, Sheet sheet) {
        CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, sheetDesc.getExcelFiledDescriptions().size() - 1);
        sheet.addMergedRegion(titleRange);
        Row titleRow = sheet.createRow(0);
        titleRow.setHeight((short) 600);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue(sheetDesc.getTitle());
    }

    /**
     * @Description: 创建日期行(需合并单元格)
     */
    private static void createTableDateRow(SheetDesc sheetDesc, Sheet sheet) {
        CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, sheetDesc.getExcelFiledDescriptions().size() - 1);
        sheet.addMergedRegion(dateRange);
        Row dateRow = sheet.createRow(1);
        dateRow.setHeight((short) 350);
        Cell dateCell = dateRow.createCell(0);
        dateCell.setCellStyle(dateStyle);
        dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
    }

    /**
     * @Description: 创建表头行(需合并单元格)
     */
    private static void creatTableHeadRow(SheetDesc sheetDesc, Sheet sheet) {
        // 表头
        Row headRow = sheet.createRow(2);
        headRow.setHeight((short) 350);
        // 序号列
        Cell snCell = headRow.createCell(0);
        snCell.setCellStyle(headStyle);
        // snCell.setCellValue("序号");
        // 列头名称
        for (int num = 0, len = sheetDesc.getExcelFiledDescriptions().size(); num < len; num++) {
            Cell headCell = headRow.createCell(num);
            headCell.setCellStyle(headStyle);
            headCell.setCellValue(sheetDesc.getExcelFiledDescriptions().get(num).getFieldDesc());
        }
    }

    /**
     * @Description: 创建所有的Sheet
     */
    private static Sheet getSheets(String sheetName) {
        return wb.createSheet(sheetName);
    }

    /**
     * @Description: 创建内容行的每一列(附加一列序号)
     */
    @SuppressWarnings("unused")
    private static Cell[] getCells(Row contentRow, int num) {
        Cell[] cells = new HSSFCell[num + 1];

        for (int i = 0, len = cells.length; i < len; i++) {
            cells[i] = contentRow.createCell(i);
            cells[i].setCellStyle(contentStyle);
        }
        // 设置序号列值，因为出去标题行和日期行，所有-2
        cells[0].setCellValue(contentRow.getRowNum() - 2);

        return cells;
    }

    /**
     * @Description: 初始化标题行样式
     */
    private static void initTitleCellStyle() {
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        titleStyle.setFont(titleFont);
        titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index);
    }

    /**
     * @Description: 初始化日期行样式
     */
    private static void initDateCellStyle() {
        dateStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        dateStyle.setFont(dateFont);
        dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index);
    }

    /**
     * @Description: 初始化表头行样式
     */
    private static void initHeadCellStyle() {
        headStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        headStyle.setFont(headFont);
        headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index);
        headStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
        headStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headStyle.setBorderRight(CellStyle.BORDER_THIN);
        headStyle.setTopBorderColor(IndexedColors.BLUE.index);
        headStyle.setBottomBorderColor(IndexedColors.BLUE.index);
        headStyle.setLeftBorderColor(IndexedColors.BLUE.index);
        headStyle.setRightBorderColor(IndexedColors.BLUE.index);
    }

    /**
     * @Description: 初始化内容行样式
     */
    private static void initContentCellStyle() {
        contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
        contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        contentStyle.setFont(contentFont);
        contentStyle.setBorderTop(CellStyle.BORDER_THIN);
        contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
        contentStyle.setBorderRight(CellStyle.BORDER_THIN);
        contentStyle.setTopBorderColor(IndexedColors.BLUE.index);
        contentStyle.setBottomBorderColor(IndexedColors.BLUE.index);
        contentStyle.setLeftBorderColor(IndexedColors.BLUE.index);
        contentStyle.setRightBorderColor(IndexedColors.BLUE.index);
        contentStyle.setWrapText(true); // 字段换行
    }

    /**
     * @Description: 初始化标题行字体
     */
    private static void initTitleFont() {
        titleFont.setFontName("华文楷体");
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleFont.setCharSet(Font.DEFAULT_CHARSET);
        titleFont.setColor(IndexedColors.BLUE_GREY.index);
    }

    /**
     * @Description: 初始化日期行字体
     */
    private static void initDateFont() {
        dateFont.setFontName("隶书");
        dateFont.setFontHeightInPoints((short) 10);
        dateFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        dateFont.setCharSet(Font.DEFAULT_CHARSET);
        dateFont.setColor(IndexedColors.BLUE_GREY.index);
    }

    /**
     * @Description: 初始化表头行字体
     */
    private static void initHeadFont() {
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short) 10);
        headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headFont.setCharSet(Font.DEFAULT_CHARSET);
        headFont.setColor(IndexedColors.BLUE_GREY.index);
    }

    /**
     * @Description: 初始化内容行字体
     */
    private static void initContentFont() {
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 10);
        contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        contentFont.setCharSet(Font.DEFAULT_CHARSET);
        contentFont.setColor(IndexedColors.BLUE_GREY.index);
    }

    /**
     * 生成标题excel单元格数据
     *
     * @param key   ：单元格名称,以分号连接
     * @param value ：单元格对应的列字段;以分号连接
     * @return
     * @Desc:
     * @date:Nov 2, 2017 3:31:08 PM
     */
    public static List<FiledDescription> splitTitle(String key, String value) {

        List<FiledDescription> list = new ArrayList<FiledDescription>();

        if (StringUtils.isNotEmpty(key) && StringUtils.isNotEmpty(value)) {
            String[] keysplit = key.split(";");
            String[] valuesplit = value.split(";");
            for (int i = 0; i < keysplit.length; i++) {
                FiledDescription fdesc = new FiledDescription(keysplit[i], valuesplit[i]);
                list.add(fdesc);
            }
        }
        return list;
    }

    /**
     * 生成标题excel单元格数据
     *
     * @param key   ：单元格名称
     * @param value ：单元格对应的列字段
     * @return
     * @Desc:
     * @date:Nov 2, 2017 3:31:08 PM
     */
    public static FiledDescription createTitle(String key, String value) {

        FiledDescription fdesc = new FiledDescription(key, value);
        return fdesc;
    }

    /**
     * @param title     :标题名称
     * @param sheetName ：sheet名称
     * @return
     * @Desc:创建sheet
     * @date:Nov 17, 2017 2:26:20 PM
     */
    public static SheetDesc createSheetDesc(String title, String sheetName) {
        // 创建sheet描述
        SheetDesc desc = new SheetDesc();
        // 设置单个sheet的表头名称
        desc.setTitle(title);
        // 设置sheet的名称
        desc.setSheetName(sheetName);
        return desc;
    }

    /**
     * 生成标题数据
     *
     * @param title        :标题名称
     * @param sheetName    ：sheet名称
     * @param descriptions ：单元格数据集
     * @return
     * @Desc:
     * @date:Nov 2, 2017 3:30:36 PM
     */
    public static SheetDesc createSheetDesc(String title, String sheetName, List<FiledDescription> descriptions) {
        // 创建sheet描述
        SheetDesc desc = new SheetDesc();
        // 设置单个sheet的表头名称
        desc.setTitle(title);
        // 设置sheet的名称
        desc.setSheetName(sheetName);
        // 列名称与实体属性关系存储到集合中
        desc.setExcelFiledDescriptions(descriptions);
        return desc;
    }

    /**
     * 生成workbook,
     *
     * @param sheetDesc ：标题数据
     * @param dataList  :excel正文数据集
     * @param isMap     :isMap 是objsMap中的List<Object>的对象是否是MAP
     * @return
     * @throws Exception
     * @Desc:
     * @date:Nov 2, 2017 3:29:10 PM
     */
    public static Workbook createWorkbook(SheetDesc sheetDesc, List<?> dataList, boolean isMap) throws Exception {
        // 获取设备信息
        LinkedHashMap<SheetDesc, List<?>> resultMap = new LinkedHashMap<SheetDesc, List<?>>();
        // 构建返回模型
        resultMap.put(sheetDesc, dataList);

        ExportExcelDataInfo dataInfo = new ExportExcelDataInfo();
        dataInfo.setObjsMap(resultMap);
        dataInfo.setMap(isMap);
        Workbook workbook = ExcelUtil.export2Excel(dataInfo);
        return workbook;
    }

    /**
     * 生成excel数据
     *
     * @param sheetDesc
     * @param dataList
     * @return
     * @throws Exception
     * @Desc:
     * @date:Nov 17, 2017 2:38:38 PM
     */
    public static LinkedHashMap<SheetDesc, List<?>> setSheetData(SheetDesc sheetDesc, List<?> dataList) throws Exception {
        // 获取设备信息
        LinkedHashMap<SheetDesc, List<?>> resultMap = new LinkedHashMap<SheetDesc, List<?>>();
        // 构建返回模型
        resultMap.put(sheetDesc, dataList);
        return resultMap;
    }

    /**
     * 创建excel导出
     *
     * @param resultMap
     * @param isMap
     * @return
     * @throws Exception
     * @Desc:
     * @date:Nov 17, 2017 2:38:56 PM
     */
    public static Workbook createWorkbook(LinkedHashMap<SheetDesc, List<?>> resultMap, boolean isMap) throws Exception {
        ExportExcelDataInfo dataInfo = new ExportExcelDataInfo();
        dataInfo.setObjsMap(resultMap);
        dataInfo.setMap(isMap);
        Workbook workbook = ExcelUtil.export2Excel(dataInfo);
        return workbook;
    }

}